USE [InventoryManagement]
GO

/****** Object:  StoredProcedure [dbo].[sp_Select_DataCabang]    Script Date: 2013/12/16 9:13:00 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_Select_DataCabang]
@ID UNIQUEIDENTIFIER=NULL,
@wfStatus VARCHAR(200) = NULL,
@ID_Router UNIQUEIDENTIFIER = NULL,
@ID_NAS UNIQUEIDENTIFIER = NULL,
@ID_Provider UNIQUEIDENTIFIER = NULL,
@NamaCabang VARCHAR(200)=NULL,
@KodeCabang VARCHAR(200)=NULL,
@NamaLokasi VARCHAR(200)=NULL,
@Alamat VARCHAR(200)=NULL,
@SerialNumber VARCHAR(200)=NULL,
@IPLAN VARCHAR(200)=NULL,
@Bandwidth INT = NULL,
@NamaNAS VARCHAR(200) = NULL,
@IPWAN VARCHAR(200) = NULL,
@IPNAS VARCHAR(200)= NULL,
@JumlahPC INT = NULL,
@JumlahNotebook INT = NULL,
@JumlahTC INT = NULL,
@Date DATETIME = NULL,
@PIC VARCHAR(200)=NULL,
@Telp VARCHAR(200)=NULL,
@HP VARCHAR(200)=NULL,
@Engineer VARCHAR(200)=NULL,
@SID VARCHAR(200)=NULL
AS
SELECT * FROM dbo.[Data Cabang] WHERE
(ID = @ID OR @ID IS NULL) AND
(wfStatus = @wfStatus OR @wfStatus IS NULL) AND
(ID_Router = @ID_Router OR @ID_Router IS NULL) AND
(ID_NAS = @ID_NAS OR @ID_NAS IS NULL) AND
(ID_Provider = @ID_Provider OR @ID_Provider IS NULL) AND
(NamaCabang = @NamaCabang OR @NamaCabang IS NULL) AND
(KodeCabang = @KodeCabang OR @KodeCabang IS NULL) AND
(NamaLokasi = @NamaLokasi OR @NamaLokasi IS NULL) AND
(Alamat = @Alamat OR @Alamat IS NULL) AND
(SerialNumber = @SerialNumber OR @SerialNumber IS NULL)AND
(IPLAN = @IPLAN OR @IPLAN IS NULL) AND
(Bandwidth = @Bandwidth OR @Bandwidth IS NULL)AND
(NamaNAS = @NamaNAS OR @NamaNas IS NULL)AND
(IPWAN = @IPWAN OR @IPWAN IS NULL)AND
(IPNAS = @IPNAS OR @IPNAS IS NULL)AND
(JumlahPC = @JumlahPC OR @JumlahPC IS NULL)AND
(JumlahNotebook = @JumlahNotebook OR @JumlahNotebook IS NULL)AND
(JumlahTC = @JumlahTC OR @JumlahTC IS NULL)AND
([Date] = @Date OR @Date IS NULL)AND
(PIC = @PIC OR @PIC IS NULL)AND
(Telp = @Telp OR @Telp IS NULL)AND
(HP = @HP OR @HP IS NULL)AND
(Engineer = @Engineer OR @Engineer IS NULL)
AND (SID = @SID OR @SID IS NULL)


GO

ALTER PROCEDURE [dbo].[sp_Select_Task]
AS
SELECT a.*,b.wfStatus FROM dbo.Approval a INNER JOIN dbo.[Data Cabang] b ON a.IDChild = b.ID
WHERE b.wfStatus LIKE 'waiting for approval'
UNION
SELECT a.*,b.wfStatus FROM dbo.Approval a INNER JOIN dbo.[CR Storage] b ON a.IDChild = b.ID
WHERE b.wfStatus LIKE 'waiting for approval'
UNION
SELECT a.*,b.wfStatus FROM dbo.Approval a INNER JOIN dbo.[CR Network] b ON a.IDChild = b.ID
WHERE b.wfStatus LIKE 'waiting for approval'
UNION
SELECT a.*,b.wfStatus FROM dbo.Approval a INNER JOIN dbo.[CR VM] b ON a.IDChild = b.ID
WHERE b.wfStatus LIKE 'waiting for approval'
UNION
SELECT a.*,b.wfStatus FROM dbo.Approval a INNER JOIN dbo.[CR Physical] b ON a.IDChild = b.ID
WHERE b.wfStatus LIKE 'waiting for approval'

GO


